#coding=utf-8
import xlrd
import decimal
import pandas as pd
from _pydecimal import Decimal, Context, ROUND_HALF_UP
from decimal import *
def export_result(num):
    num_x, num_y = str(num).split('.')
    num = float(num_x + '.' + num_y[0:2])
    return num

def monthss(str1, str2):
    year1 = datetime.datetime.strptime(str1, "%Y-%m-%d").year
    year2 = datetime.datetime.strptime(str2, "%Y-%m-%d").year
    month1 = datetime.datetime.strptime(str1, "%Y-%m-%d").month
    month2 = datetime.datetime.strptime(str2, "%Y-%m-%d").month
    num = (year1 - year2) * 12 + (month1 - month2)
    return num


bk = xlrd.open_workbook(r"D:\jiaqi.xls")
shxrange = range(bk.nsheets)
try:
    sh = bk.sheet_by_name("Sheet")
    # prin  t(sh)
except:
    print("no sheet in %s named Sheet1")
# 获取行数
nrows = sh.nrows
# 获取列数
ncols = sh.ncols
import datetime
import xlrd
import decimal
import pandas as pd

###读取上个月和这个月的试用期列表
last_mouth=[]
instant_mouth=[]

def last():
    df = pd.read_excel('D:\\aaa\last.xls')
    bk = xlrd.open_workbook(r"D:\\aaa\last.xls")
    shxrange = range(bk.nsheets)
    try:
        sh = bk.sheet_by_name("Sheet1")
        # prin  t(sh)
    except:
        print("no sheet in %s named Sheet1")
    # 获取行数
    nrows = sh.nrows
    # 获取列数
    ncols = sh.ncols
    for i in range(1, nrows):
        row_data = sh.row_values(i)
        print(row_data,"上个月的")
        last_mouth.append(row_data[0])

def instant():
    df = pd.read_excel('D:\\aaa\in.xls')
    bk = xlrd.open_workbook(r"D:\\aaa\in.xls")
    shxrange = range(bk.nsheets)
    try:
        sh = bk.sheet_by_name("Sheet1")
        # prin  t(sh)
    except:
        print("no sheet in %s named Sheet1")
    # 获取行数
    nrows = sh.nrows
    # 获取列数
    ncols = sh.ncols
    for i in range(1, nrows):
        row_data = sh.row_values(i)
        instant_mouth.append(row_data[0])


last()
instant()



df = pd.read_excel('D:\jiaqi.xls')
bk = xlrd.open_workbook(r"D:\jiaqi.xls")
shxrange = range(bk.nsheets)
try:
    sh = bk.sheet_by_name("Sheet")
    # prin  t(sh)
except:
    print("no sheet in %s named Sheet1")
# 获取行数
nrows = sh.nrows
# 获取列数
ncols = sh.ncols
import datetime
Mouth = datetime.datetime.now().month
from dateutil import rrule




#nowTime=datetime.datetime.now().strftime('%Y-%m-%d') # 现在

nowTime="2020-12-16"
release=[]#本月释放列表
full=["14047","13001","13033","10006","15412"]#工作满20年的 员工
for i in range(1, nrows):
    row_data = sh.row_values(i)
    print(row_data)
    if i==1 or i==2 or row_data[4]=="":
        pass
    else:

        #上个月和本月都不是试用期
        if row_data[3] not  in last_mouth and row_data[3] not in instant_mouth:

            #代表不是实习生
            OP = i
            i = i - 1

            b = row_data[4].split("-")

            months = monthss(nowTime, row_data[4])  # 新版本计算月份之前bug

            # 保留2位小时需要重新计算

            if int(b[2]) > 15:
                months = months - 1
            mouth_day = months / 12
            mouth_day = str(mouth_day)

            # 这边做判断小于代表这个月有假日，否则代表这个月没有假期
            if row_data[3] in full:

                '''20年'''
                B = df.iloc[i][6] + 10
                B = float(Decimal(B).quantize(Decimal('0.00')))
                import xlrd  # 导入模块
                from xlutils.copy import copy  # 导入copy模块

                rb = xlrd.open_workbook('D:\jiaqi.xls')  # 打开weng.xls文件
                wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
                ws = wb.get_sheet("Sheet")  # 获取表单0
                ws.write(OP, 6, B)
                wb.save('D:\jiaqi.xls')
            else:
                if mouth_day.split(".")[0] == "0":
                    '''转正没有满1年按照10天加'''
                    omouth = 10 / 12
                    B = df.iloc[i][6] + 0.83 * 8
                    import xlrd  # 导入模块
                    from xlutils.copy import copy  # 导入copy模块
                    rb = xlrd.open_workbook('D:\jiaqi.xls')  # 打开weng.xls文件
                    wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
                    ws = wb.get_sheet("Sheet")  # 获取表单0
                    ws.write(OP, 6, B)

                    wb.save('D:\jiaqi.xls')
                    # print(leave_days,"1111",row_data)
                else:
                    '''转正满一年'''
                    data_if = row_data[4] < "2016-06-01"
                    '''代表工作年限满1年的'''
                    if data_if:
                        '''16年之前转正的另算'''
                        d1 = "2016-06-01"
                        months = monthss(d1, row_data[4])
                        mouth_day = months / 12  # 算出多少年
                        mouth_day = str(mouth_day)
                        uday = mouth_day.split(".")[0]
                        uday = int(uday)
                        new_2016_data = {"12029": 15, "12040": 15, "13079": 15, "14043": 15, "14094": 15, "14178": 12,
                                         "14204": 12, "15255": 12, "15283": 12, "15376": 12, "15422": 10, "15468": 10,
                                         "15474": 10,"13036":12 ,"12014":13,"11019":12,"12045":13,"15311":11,"12038":12,"14124":11,"14232":11,"12008":11,"10009":13}
                        if row_data[3] in new_2016_data.keys():
                            six_number = new_2016_data[row_data[3]]
                        else:
                            six_number = 5 + uday
                        if six_number < 10:
                            six_number = 10
                        else:
                            pass
                        months = monthss(nowTime, d1)
                        mouth_day = months / 12  # 算出多少年
                        mouth_day = str(mouth_day)
                        uday = mouth_day.split(".")[0]
                        Year_day = six_number + int(uday)
                        print(Year_day, uday, row_data)
                        if Year_day > 15:
                            Year_day = 15
                        omouth = Year_day / 12
                        leave_days = float(Decimal(str(Year_day / 12)).quantize(Decimal('0.00')))
                        print(leave_days, type(leave_days), "111111")
                        print(leave_days)
                        B = df.iloc[i][6] + leave_days * 8
                        B = float(Decimal(B).quantize(Decimal('0.00')))
                        import xlrd  # 导入模块
                        from xlutils.copy import copy  # 导入copy模块
                        rb = xlrd.open_workbook('D:\jiaqi.xls')  # 打开weng.xls文件
                        wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
                        ws = wb.get_sheet("Sheet")  # 获取表单0
                        ws.write(OP, 6, B)
                        wb.save('D:\jiaqi.xls')
                    else:
                        uday = mouth_day.split(".")[0]
                        uday = int(uday)
                        Year_day = 10 + uday
                        # print(type(Year_day))
                        if Year_day > 15:
                            Year_day = 15
                        leave_days = float(Decimal(str(Year_day / 12)).quantize(Decimal('0.00')))
                        print(leave_days, type(leave_days), "11111111", uday, mouth_day)
                        B = df.iloc[i][6] + leave_days * 8
                        B = float(Decimal(B).quantize(Decimal('0.00')))
                        import xlrd  # 导入模块
                        from xlutils.copy import copy  # 导入copy模块
                        rb = xlrd.open_workbook('D:\jiaqi.xls')  # 打开weng.xls文件
                        wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
                        ws = wb.get_sheet("Sheet")  # 获取表单0
                        ws.write(OP, 6, B)
                        wb.save('D:\jiaqi.xls')
                        # print("已经保存")

        else:
            #代表上个月和这个月有可能是实习生
            OP = i
            i = i - 1

            if row_data[3] in  last_mouth and row_data[3] in instant_mouth:
                print("这个月也是实习生",row_data)
                pass
            elif row_data[3] in last_mouth and row_data[3] not  in instant_mouth:
                b = row_data[4].split("-")
                months = monthss(nowTime, row_data[4])
                if int(b[2]) > 15:
                    months = months - 1
                mouth_day = months / 12
                mouth_day = str(mouth_day)
                print(row_data,"Hey 我转正了 这个月")
                if row_data[3] in release:
                    "这边如果他手动释放了 ，这边需要单独给他加上本月的年假"
                    B = df.iloc[i][6] + 0.83 * 8
                    import xlrd  # 导入模块
                    from xlutils.copy import copy  # 导入copy模块

                    rb = xlrd.open_workbook('D:\jiaqi.xls')  # 打开weng.xls文件
                    wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
                    ws = wb.get_sheet("Sheet")  # 获取表单0R
                    ws.write(OP, 6, B)

                    wb.save('D:\jiaqi.xls')
                else:
                    print("我在这里",mouth_day,months)
                    if mouth_day.split(".")[0] == "0":
                        months = monthss(nowTime, row_data[4])
                        if int(b[2]) <= 15:
                            months = months + 1
                        else:
                            months=months
                        print("我在这里")
                        '''转正没有满1年按照10天加'''
                        omouth = 10 / 12
                        B =  0.83  * 8 * months
                        B = float(Decimal(B).quantize(Decimal('0.00')))
                        import xlrd  # 导入模块
                        from xlutils.copy import copy  # 导入copy模块
                        print(B,"应该给我释放的年假")
                        rb = xlrd.open_workbook('D:\jiaqi.xls')  # 打开weng.xls文件
                        wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
                        ws = wb.get_sheet("Sheet")  # 获取表单0
                        print("112233445566")
                        ws.write(OP, 6, B)

                        wb.save('D:\jiaqi.xls')
                        # print(leave_days,"1111",row_data)
                    else:
                        '''入职日期到现在一共有几个月，就释放多少月年假'''

                        new_months= monthss(nowTime, row_data[4])#代表满几个月
                        new_b = row_data[4].split("-")
                        div = new_months// 12
                        mod = new_months % 12
                        new_months_list = []  # 代表一共释放的年假数
                        new_b = row_data[4]
                        new_b = new_b.split("-")
                        if int(new_b[2]) > 15:
                            # 这里算第一年应该释放几个月的年假15号之前本月没年假少一
                            first_month = 11
                        else:
                            first_month = 12

                        num_number = 0
                        for num in range(1, div + 1):
                            # num+1代表他入职几年 加1代表循环到几 for 循环会自动少一个数字
                            append_year = num - 1  # 这个代表增加的年假数*12 ，例子 1年零几个月就是10/12*8*12+ 11*零几个月

                            if num_number == 0:

                                Day = float(
                                    Decimal(str((10 + append_year) / 12)).quantize(Decimal('0.00'))) * 8 * first_month
                                Day = float(Decimal(Day).quantize(Decimal('0.00')))
                                new_months_list.append(Day)
                            else:
                                # 控制15天的

                                special = 10 + append_year

                                if special > 15:
                                    special = 15

                                Day = float(Decimal(str((special) / 12)).quantize(Decimal('0.00'))) * 8 * 12
                                Day = float(Decimal(Day).quantize(Decimal('0.00')))
                                new_months_list.append(Day)
                            num_number += 1
                            # 最后计算他的余数几个月的年假
                            outer = 10 + num
                            if outer > 15:
                                outer = 15

                            Day = float(Decimal(str((outer) / 12)).quantize(Decimal('0.00'))) * 8 * mod
                            Day = float(Decimal(Day).quantize(Decimal('0.00')))
                            new_months_list.append(Day)
                        print(new_months_list)
                        print(sum(new_months_list))
                        #这边开始将他释放的年假写入表格然后进行释放
                        import xlrd  # 导入模块
                        from xlutils.copy import copy  # 导入copy模块

                        rb = xlrd.open_workbook('D:\jiaqi.xls')  # 打开weng.xls文件
                        wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
                        ws = wb.get_sheet("Sheet")  # 获取表单0
                        ws.write(OP, 6, sum(new_months_list))

                        wb.save('D:\jiaqi.xls')
            else:
                pass

